<?php

/**
 * Created by PhpStorm.
 * User: zuoa
 * Date: 2016/11/21 0021
 * Time: 09:56
 */
class Record_Model extends CI_Model
{
    public $userdata = null;

    public function __construct()
    {
        $this->load->database();
    }

    private function is_admin()
    {
        return $this->userdata != null && (((int)$this->userdata["is_admin"]) == 1);
    }

    public function get_records($num = 10)
    {
        if ($this->is_admin()) {
            $sql = "SELECT r.id, r.related_info, r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid WHERE DATE(r.ts) = CURDATE() ORDER BY r.ts DESC LIMIT ?";
            $query = $this->db->query($sql, (int)($num));
            return $query->result_array();
        } else {
            $sql = "SELECT r.id, r.related_info, r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid WHERE DATE(r.ts) = CURDATE() AND r.department=? ORDER BY r.ts DESC LIMIT ?";
            $query = $this->db->query($sql, array($this->userdata["userdept"], (int)($num)));
            return $query->result_array();
        }
    }

    public function get_police_records($id, $offset, $limit)
    {
        $sql = "SELECT r.id, r.related_info, r.type,r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid WHERE r.openid = ? ORDER BY r.ts DESC LIMIT ?,?";
        $query = $this->db->query($sql, array($id, (int)$offset, (int)$limit));
        return $query->result_array();
    }

    public function get_today_records()
    {
        if ($this->is_admin()) {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE(r.ts) = CURDATE() ORDER BY r.ts DESC";

            $query = $this->db->query($sql);
            return $query->result_array();
        } else {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE(r.ts) = CURDATE() AND r.department=? ORDER BY r.ts DESC";

            $query = $this->db->query($sql, $this->userdata["userdept"]);
            return $query->result_array();
        }
    }

    public function get_month_records($dept = '')
    {
        if ($this->is_admin()) {
            if ($dept == '') {
                $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') ORDER BY r.ts DESC";

                $query = $this->db->query($sql);
                return $query->result_array();
            } else {
                $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') AND r.department=?  ORDER BY r.ts DESC";

                $query = $this->db->query($sql, $dept);
                return $query->result_array();
            }
        } else {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') AND r.department=?  ORDER BY r.ts DESC";

            $query = $this->db->query($sql, $this->userdata["userdept"]);
            return $query->result_array();
        }
    }

    public function get_month_count($dept = '')
    {
        if ($dept == '') {
            $where = "DATE_FORMAT(ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m')";
            return $this->db->where($where)->count_all_results("tbl_accident_record");
        } else {
            $where = "DATE_FORMAT(ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') AND department ='" . $dept . "'";
            return $this->db->where($where)->count_all_results("tbl_accident_record");
        }
    }

    public function get_page_records($dept = '', $offset, $limit)
    {
        if ($dept == '') {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') ORDER BY r.ts DESC LIMIT ?,?";

            $query = $this->db->query($sql, array((int) $offset, (int) $limit));
            return $query->result_array();
        } else {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid  WHERE DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') AND r.department=?  ORDER BY r.ts DESC LIMIT ?,?";

            $query = $this->db->query($sql, array($dept, (int) $offset, (int) $limit));
            return $query->result_array();
        }
    }

    public function get_heatmap_records($num = 10000)
    {
        if ($this->is_admin()) {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid ORDER BY r.ts DESC LIMIT ?";

            $query = $this->db->query($sql, (int)($num));
            return $query->result_array();
        } else {
            $sql = "SELECT r.id, r.related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid WHERE r.department=? ORDER BY r.ts DESC LIMIT ?";

            $query = $this->db->query($sql, array($this->userdata["userdept"], (int)($num)));
            return $query->result_array();
        }
    }

    public function search_record($s)
    {
        $like = '%' . $this->db->escape_like_str($s) . '%';
        if ($this->is_admin()) {
            $sql = "SELECT r.id, r.related_info, r.adjust_related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid WHERE r.related_info like ? ORDER BY r.ts DESC LIMIT 20";
            $query = $this->db->query($sql, $like);
            return $query->result_array();
        } else {
            $sql = "SELECT r.id, r.related_info,r.adjust_related_info,r.type, r.lat,r.lng, r.ts, u.name, u.department, u.phone FROM tbl_accident_record as r  LEFT JOIN tbl_police as u ON u.openid = r.openid WHERE r.related_info like ? AND r.department=? ORDER BY r.ts DESC LIMIT 20";
            $query = $this->db->query($sql, array($like, $this->userdata["userdept"]));
            return $query->result_array();
        }
    }

    public function add_record($r)
    {
        if ($r["type"] == null || (int)$r["type"] == 0) {
            $query = $this->db->where("related_info", $r["related_info"])->get("tbl_accident_record");
            if ($query->num_rows() > 0) {
                return null;
            }
        }

        return $this->db->insert("tbl_accident_record", $r);
    }

    public function adjust($related, $adjust_related)
    {
        $data = array(
            'adjust_related_info' => $adjust_related
        );

        $this->db->update("tbl_accident_record", $data, array('related_info' => $related));
    }
    public function get_proc_data_by_day($num)
    {
        if ($this->is_admin()) {
            $sql = "SELECT DATE_FORMAT(ts,'%Y-%m-%d') day, COUNT(id) as count FROM tbl_accident_record  GROUP BY day ORDER BY day DESC LIMIT ?;";
            $query = $this->db->query($sql, (int)($num));
            return $query->result_array();
        } else {
            $sql = "SELECT DATE_FORMAT(ts,'%Y-%m-%d') day, COUNT(id) as count FROM tbl_accident_record WHERE department=?  GROUP BY day ORDER BY day DESC LIMIT ?;";
            $query = $this->db->query($sql, array($this->userdata["userdept"], (int)($num)));
            return $query->result_array();
        }
    }

    public function get_proc_data_by_week($num)
    {
        if ($this->is_admin()) {
            $sql = "SELECT concat(DATE_FORMAT(SUBDATE(ts,DATE_FORMAT(ts,'%w')-1), '%Y%m%d'),' \n ', DATE_FORMAT(SUBDATE(ts,DATE_FORMAT(ts,'%w')-7), '%Y%m%d')) week, COUNT(id) as count FROM tbl_accident_record  GROUP BY week ORDER BY week DESC LIMIT ?;";
            $query = $this->db->query($sql, (int)($num));
            return $query->result_array();
        } else {
            $sql = "SELECT concat(DATE_FORMAT(SUBDATE(ts,DATE_FORMAT(ts,'%w')-1), '%Y%m%d'),' \n ', DATE_FORMAT(SUBDATE(ts,DATE_FORMAT(ts,'%w')-7), '%Y%m%d')) week, COUNT(id) as count FROM tbl_accident_record WHERE department=?   GROUP BY week ORDER BY week DESC LIMIT ?;";
            $query = $this->db->query($sql, array($this->userdata["userdept"], (int)($num)));
            return $query->result_array();
        }
    }

    public function get_proc_data_by_month($num)
    {
        if ($this->is_admin()) {
            $sql = "SELECT DATE_FORMAT(ts,'%Y-%m') month, COUNT(id) as count FROM tbl_accident_record  GROUP BY month ORDER BY month DESC LIMIT ?;";
            $query = $this->db->query($sql, (int)($num));
            return $query->result_array();
        } else {
            $sql = "SELECT DATE_FORMAT(ts,'%Y-%m') month, COUNT(id) as count FROM tbl_accident_record  WHERE department=?   GROUP BY month ORDER BY month DESC LIMIT ?;";
            $query = $this->db->query($sql, array($this->userdata["userdept"], (int)($num)));
            return $query->result_array();
        }
    }

    public function get_today_process_statistics_by_police($num = 10)
    {
        if ($this->is_admin()) {
            $sql = "SELECT name, u.department, COUNT(*) as count FROM tbl_accident_record as r LEFT JOIN tbl_police as u ON r.openid=u.openid WHERE u.name > '' AND DATE(ts) = CURDATE()  GROUP BY name ORDER BY count DESC";
            if ($num > 0) {
                $sql = $sql." LIMIT ".$num;
            }
            $query = $this->db->query($sql);
            return $query->result_array();
        } else {
            $sql = "SELECT name, u.department, COUNT(*) as count FROM tbl_accident_record as r LEFT JOIN tbl_police as u ON r.openid=u.openid WHERE u.name > '' AND r.department=? AND DATE(ts) = CURDATE() GROUP BY name ORDER BY count DESC";
            if ($num > 0) {
                $sql = $sql." LIMIT ".$num;
            }

            $query = $this->db->query($sql, $this->userdata["userdept"]);
            return $query->result_array();
        }
    }


    public function get_today_data_by_department($num)
    {
        $sql = "SELECT department, COUNT(*) as count FROM tbl_accident_record WHERE DATE(ts) = CURDATE()  GROUP BY department ORDER BY count DESC LIMIT 10;";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function get_process_statistics_by_department()
    {
        $sql = "SELECT department, COUNT(*) as count FROM tbl_accident_record WHERE DATE_FORMAT(ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m')  GROUP BY department ORDER BY count DESC LIMIT 10;";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function get_process_statistics_by_police($num = 10)
    {
        if ($this->is_admin()) {
            $sql = "SELECT name, COUNT(*) as count FROM tbl_accident_record as r LEFT JOIN tbl_police as u ON r.openid=u.openid WHERE u.name > '' AND DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m')  GROUP BY name ORDER BY count DESC";
            if ($num > 0) {
                $sql = $sql." LIMIT ".$num;
            }
            $query = $this->db->query($sql);
            return $query->result_array();
        } else {
            $sql = "SELECT name, COUNT(*) as count FROM tbl_accident_record as r LEFT JOIN tbl_police as u ON r.openid=u.openid WHERE u.name > '' AND r.department=? AND DATE_FORMAT(r.ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m') GROUP BY name ORDER BY count DESC";
            if ($num > 0) {
                $sql = $sql." LIMIT ".$num;
            }

            $query = $this->db->query($sql, $this->userdata["userdept"]);
            return $query->result_array();
        }
    }


    public function get_accident_detail($aid)
    {
        $query = $this->db->where("SA_SaNum", $aid)->get("view_accident_detail");
        return $query->result_array();
    }

    public function get_complete_count()
    {
        if ($this->is_admin()) {
            return $this->db->count_all_results("view_accident_complete");
        } else {
            if ($this->userdata == null) {
                return 0;
            }

            return $this->db->where("department", $this->userdata["userdept"])->count_all_results("view_accident_complete");
        }
    }

    public function get_require_locate_num()
    {
        if ($this->is_admin()) {
            return $this->db->count_all_results("view_require_locate");
        } else {
            if ($this->userdata == null) {
                return 0;
            }

            return $this->db->where("SA_Dtm", $this->userdata["userdept"])->count_all_results("view_require_locate");
        }
    }

    public function get_require_type_in_num()
    {
        if ($this->is_admin()) {
            return $this->db->count_all_results("view_require_type_in");
        } else {
            if ($this->userdata == null) {
                return 0;
            }

            return $this->db->where("department", $this->userdata["userdept"])->count_all_results("view_require_type_in");
        }
    }
    public function get_complete_records($offset, $limit)
    {
        if ($this->is_admin()) {
            $query = $this->db->offset($offset)->limit($limit)->order_by("ts", "desc")->get("view_accident_complete");
            return $query->result_array();
        } else {
            if ($this->userdata == null) {
                return array();
            }

            $query = $this->db->where("department", $this->userdata["userdept"])->offset($offset)->limit($limit)->order_by("ts", "desc")->get("view_accident_complete");
            return $query->result_array();
        }
    }

    public function get_require_locate_records($offset, $limit)
    {
        if ($this->is_admin()) {
            $query = $this->db->offset($offset)->limit($limit)->order_by("SA_HTime", "desc")->get("view_require_locate");
            return $query->result_array();
        } else {
            if ($this->userdata == null) {
                return array();
            }
            $query = $this->db->where("SA_Dtm", $this->userdata["userdept"])->offset($offset)->limit($limit)->order_by("SA_HTime", "desc")->get("view_require_locate");
            return $query->result_array();
        }
    }

    public function get_require_type_in_records($offset, $limit)
    {
        if ($this->is_admin()) {
            $query = $this->db->offset($offset)->limit($limit)->order_by("ts", "desc")->get("view_require_type_in");
            return $query->result_array();
        } else {
            if ($this->userdata == null) {
                return array();
            }

            $query = $this->db->where("department", $this->userdata["userdept"])->offset($offset)->limit($limit)->order_by("ts", "desc")->get("view_require_type_in");
            return $query->result_array();
        }
    }

    public function ignore_locate($id)
    {
        $data = array(
            'ignore_locate' => 1
        );

        $this->db->update("view_require_locate", $data, array('SA_SaNum' => $id));
    }



    public function ignore_typein($id)
    {
        $data = array(
            'type' => 99
        );

        $this->db->update("view_require_type_in", $data, array('related_info' => $id, "type" => 0));
    }

}